﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using DTO;
using UTIL;

namespace DAO
{
    public class SanPhamDao
    { 
        public static bool ThemSanPham(SanPhamDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_themSanPham");
                StoreUtilities.AddInputPara(cmd, "@maloai", SqlDbType.Int, one.IDLoai);
                StoreUtilities.AddInputPara(cmd, "@tensanpham", SqlDbType.VarChar, one.TenSanPham);
                StoreUtilities.AddInputPara(cmd, "@hinhanh", SqlDbType.VarChar, one.Hinh);
                StoreUtilities.AddInputPara(cmd, "@dongia", SqlDbType.Int, one.DonGia);
                StoreUtilities.AddInputPara(cmd, "@ngaynhap", SqlDbType.DateTime, one.NgayNhap);
                StoreUtilities.AddInputPara(cmd, "@soluongcon", SqlDbType.Int, one.SoLuongCon);
                StoreUtilities.AddInputPara(cmd, "@soluongdaban", SqlDbType.Int, one.SoLuongDaBan);
                StoreUtilities.AddInputPara(cmd, "@maNSX", SqlDbType.Int, one.IDNhaSanXuat);
                StoreUtilities.AddInputPara(cmd, "@diembinhchon", SqlDbType.Int, one.DiemBinhChon);
                StoreUtilities.AddInputPara(cmd, "@idgiamgia", SqlDbType.Int, one.IDGiamGia);
                StoreUtilities.AddInputPara(cmd, "@idkhuyenmai", SqlDbType.Int, one.IDKhuyenMai);
                                
                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;
        }

        public static bool xoaSanPham(int id)
        {
            bool result = false;
            try
            {
                //Xoá các hoá đơn.
                List<ChiTietHoaDonDto> ds =ChiTietHoaDonDao.LayDanhSachChiTietHoaDonTheoIDSanPham(id);
                if (ds.Count != 0)
                {
                    if (ChiTietHoaDonDao.XoaChiTietHoaDonTheoIDSanPham(id) == false)
                        return false;
                }
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_xoaSanPham");
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, id);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
            }
            return result;
        }

        public static bool CapnhatSanPham(SanPhamDto one)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_capnhatSanPham");
                StoreUtilities.AddInputPara(cmd, "@maloai", SqlDbType.Int, one.IDLoai);
                StoreUtilities.AddInputPara(cmd, "@tensanpham", SqlDbType.VarChar, one.TenSanPham);
                StoreUtilities.AddInputPara(cmd, "@hinhanh", SqlDbType.VarChar, one.Hinh);
                StoreUtilities.AddInputPara(cmd, "@dongia", SqlDbType.Int, one.DonGia);
                StoreUtilities.AddInputPara(cmd, "@ngaynhap", SqlDbType.DateTime, one.NgayNhap);
                StoreUtilities.AddInputPara(cmd, "@soluongcon", SqlDbType.Int, one.SoLuongCon);
                StoreUtilities.AddInputPara(cmd, "@soluongdaban", SqlDbType.Int, one.SoLuongDaBan);
                StoreUtilities.AddInputPara(cmd, "@maNSX", SqlDbType.Int, one.IDNhaSanXuat);
                StoreUtilities.AddInputPara(cmd, "@IDgiamgia", SqlDbType.Int, one.IDGiamGia);
                StoreUtilities.AddInputPara(cmd, "@IDkhuyenmai", SqlDbType.Int, one.IDKhuyenMai);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, one.ID);

                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;
        }

        public static bool CapnhatDiemSanPham(int diem, int solandanhgia, int IDsanpham)
        {
            bool result = false;
            try
            {
                // tao ket noi CSDL 
                SqlConnection cnn = DataConnector.MakeConnection();

                // goi proc thuc thi them loai nguoi dung
                SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_capnhatDiemSanPham");
                StoreUtilities.AddInputPara(cmd, "@diembinhchon", SqlDbType.Int, diem);
                StoreUtilities.AddInputPara(cmd, "@solandanhgia", SqlDbType.Int, solandanhgia);
                StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, IDsanpham);
               
                int i = cmd.ExecuteNonQuery();
                if (i == 1)
                    result = true;

                // dong ket noi
                cnn.Close();
            }
            catch (Exception ex)
            {
                // bat loi 
                throw ex;

            }
            return result;
        }

        public static SanPhamDto LaySanPhamTheoID(int ID)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_laySanPham");
            StoreUtilities.AddInputPara(cmd, "@ID", SqlDbType.Int, ID);

            SqlDataReader dr = cmd.ExecuteReader();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

            }
            // dong ket noi
            cnn.Close();

            return sanpham;
        }

        public static SanPhamDto LaySanPhamTheoTen(String tensanpham)
        {
            // ket noi CSDL
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_laySanPhamThepTen");
            StoreUtilities.AddInputPara(cmd, "@tensanpham", SqlDbType.VarChar, tensanpham);

            SqlDataReader dr = cmd.ExecuteReader();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                //sanpham.IDGiamGia = (int)dr["IDGiamGia"];
               // sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

            }
            // dong ket noi
            cnn.Close();

            return sanpham;
        }

        public static List<SanPhamDto> LayDanhSachSanPham()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPham");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static int LaySoLuongSanPhamTheoKey(string key)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select * from SanPham where tensanpham like N'%" + key + "%'";
            sql += " or mansx in (select ID from nhasanxuat where ten like N'%" + key + "%')";
            sql += " or maloai in (select ID from loaisanpham where tenloaisanpham like N'%" + key + "%')";
            int num;
            bool res=int.TryParse(key,out num);
            if (res == true)
            {
                sql += " or dongia="+num;
                sql += "or soluongcon=" + num;
                sql += "or soluongdaban=" + num;
                
            }
            DateTime date;
            res = DateTime.TryParse(key, out date);
            if (res == true)
            {
                sql += "or ngaynhap='" + key + "'";
            }
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();
            return danhsach.Count;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamTheoKeyPaging(int start, int end, string key)
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select ID,maloai,tensanpham,hinhanh,dongia,ngaynhap,soluongcon,soluongdaban,maNSX,diembinhchon,idgiamgia,idkhuyenmai  from (select ROW_NUMBER() over (order by ID)as row,* from sanpham where ";
            sql += " tensanpham like N'%" + key + "%'";
            sql += " or mansx in (select ID from nhasanxuat where ten like N'%" + key + "%')";
            sql += " or maloai in (select ID from loaisanpham where tenloaisanpham like N'%" + key + "%')";
            int num;
            bool res = int.TryParse(key, out num);
            if (res == true)
            {
                sql += " or dongia=" + num;
                sql += "or soluongcon=" + num;
                sql += "or soluongdaban=" + num;

            }
            DateTime date;
            res = DateTime.TryParse(key, out date);
            if (res == true)
            {
                sql += "or ngaynhap='" + key+"'" ;
            }
            sql += ") AS LogWithRowNumbers where row>=" + start + " and row<=" + end;
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();
            return danhsach;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamPaging(int start, int end)
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select ID,maloai,tensanpham,hinhanh,dongia,ngaynhap,soluongcon,soluongdaban,maNSX,diembinhchon,idgiamgia,idkhuyenmai  from (select ROW_NUMBER() over (order by ID)as row,* from sanpham) AS LogWithRowNumbers where row>="+start+" and row<="+end;
            SqlCommand cmd = new SqlCommand(sql,cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamTheoLoai(int maloai)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamTheoLoai");
            StoreUtilities.AddInputPara(cmd, "@matheloai", SqlDbType.Int, maloai);

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamTheoLoaiPaging(int start, int end,int id)
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select ID,maloai,tensanpham,hinhanh,dongia,ngaynhap,soluongcon,soluongdaban,maNSX,diembinhchon,idgiamgia,idkhuyenmai  from (select ROW_NUMBER() over (order by ID)as row,* from sanpham where maloai="+id+" ) AS LogWithRowNumbers where row>=" + start + " and row<=" + end;
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static int LaySoLuongSanPhamTheoLoaiKey(string key,int id)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select * from SanPham where (tensanpham like N'%" + key + "%'";
            sql += " or mansx in (select ID from nhasanxuat where ten like N'%" + key + "%')";
            sql += " or maloai in (select ID from loaisanpham where tenloaisanpham like N'%" + key + "%')";
            int num;
            bool res = int.TryParse(key, out num);
            if (res == true)
            {
                sql += " or dongia=" + num;
                sql += "or soluongcon=" + num;
                sql += "or soluongdaban=" + num;

            }
            DateTime date;
            res = DateTime.TryParse(key, out date);
            if (res == true)
            {
                sql += "or ngaynhap='" + key + "'";
            }
            sql += ") and maloai=" + id;
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();
            return danhsach.Count;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamTheoLoaiKeyPaging(int start, int end, string key,int id)
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "select ID,maloai,tensanpham,hinhanh,dongia,ngaynhap,soluongcon,soluongdaban,maNSX,diembinhchon,idgiamgia,idkhuyenmai  from (select ROW_NUMBER() over (order by ID)as row,* from sanpham where ";
            sql += " (tensanpham like N'%" + key + "%'";
            sql += " or mansx in (select ID from nhasanxuat where ten like N'%" + key + "%')";
            sql += " or maloai in (select ID from loaisanpham where tenloaisanpham like N'%" + key + "%')";
            int num;
            bool res = int.TryParse(key, out num);
            if (res == true)
            {
                sql += " or dongia=" + num;
                sql += "or soluongcon=" + num;
                sql += "or soluongdaban=" + num;

            }
            DateTime date;
            res = DateTime.TryParse(key, out date);
            if (res == true)
            {
                sql += "or ngaynhap='" + key + "'";
            }
            sql += ") and maloai="+id+") AS LogWithRowNumbers where row>=" + start + " and row<=" + end;
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();
            return danhsach;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamMoi()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamMoi");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                //sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                //sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<SanPhamDto> LayDanhSachSanKhuyenMai()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamKhuyenMai");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];
                sanpham.IDGiamGia = sanpham.DonGia * (100 - (int)dr["hinhthuc"]) / 100;

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<SanPhamDto> LayDanhSachSanKhuyenMaiLimit()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamKhuyenMaiLimit");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];
                sanpham.IDGiamGia = sanpham.DonGia * (100 - (int)dr["hinhthuc"]) / 100;

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }


        public static List<SanPhamDto> LayDanhSachSanPhamGiamGia()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamGiamGia");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                //sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<SanPhamDto> LayDanhSachSanPhamDuocYeuThich()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamDuocYeuThich");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                //sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                //sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];

                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<SanPhamDto> LayDanhSachSanPhamBanChay()
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamBanChay");

            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                //sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                //sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];


                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<SanPhamDto> LayDanhSachSanPhamTimKiemNhanh(String keyword, int pageSize, int pageNum)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamTimKiemNhanh");
            StoreUtilities.AddInputPara(cmd, "@keyword", SqlDbType.NVarChar, keyword);
            StoreUtilities.AddInputPara(cmd, "@pageSize", SqlDbType.Int, pageSize);
            StoreUtilities.AddInputPara(cmd, "@pageNum", SqlDbType.Int, pageNum);
            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];


                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static int LayTatCaSanPhamTimKiemNhanh(String keyword)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layTatCaSanPhamTimKiemNhanh");
            StoreUtilities.AddInputPara(cmd, "@keyword", SqlDbType.NVarChar, keyword);

            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                int ketqua;
                ketqua = (int)dr[0];
                cnn.Close();
                return ketqua;
            }
            cnn.Close();
            return 0;
        }

        public static List<SanPhamDto> LayDanhSachSanPhamTimKiemNC(String keyword, int loai, int giaTu, int giaDen, String khuyenmai, int pageSize, int pageNum)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();
            int start = (pageNum - 1) * pageSize + 1;
            int end = pageNum * pageSize;
            String sql = "( select ROW_NUMBER() OVER( order by tensanpham ) as RowNum , * from sanpham where tensanpham like '%" + keyword + "%' ";
            if (loai != 0)
                sql += " and maloai = " + loai;
            if (giaTu != 0)
            {
                if (giaTu != 5)
                    sql += " and dongia >= " + giaTu * 100;
                if (giaTu == 5)
                    sql += " and dongia <= " + "100";
            }
            if (giaDen != 0)
            {

                if (giaDen == 4)
                    sql += " and dongia <=" + "800";
                else
                {
                    if (giaDen == 5)
                        sql += " and dongia <=" + "100";
                    else
                        sql += " and dongia <=" + giaDen * 100;
                }
            }
            if (khuyenmai == "False")
                sql += " and IDkhuyenmai = 2 ";
            if (khuyenmai == "True")
                sql += " and (IDkhuyenmai > 2 or IDkhuyenmai < 2)";
            sql += " ) as s ";
            String sql1 = "select * from " + sql;
            sql1 += " where RowNum between " + start + " and " + end;
            SqlCommand cmd = new SqlCommand(sql1, cnn);
            SqlDataReader dr = cmd.ExecuteReader();
            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];


                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        public static int LayTatCaSanPhamTimKiemNC(String keyword, int loai, int giaTu, int giaDen, String khuyenmai)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            String sql = " select Count(*) from sanpham where tensanpham like '%" + keyword + "%' ";
            if (loai != 0)
                sql += " and maloai = " + loai;
            if (giaTu != 0)
            {
                if (giaTu != 5)
                    sql += " and dongia >= " + giaTu * 100;
                if (giaTu == 5)
                    sql += " and dongia <= " + "100";
            }
            if (giaDen != 0)
            {

                if (giaDen == 4)
                    sql += " and dongia <=" + "800";
                else
                {
                    if (giaDen == 5)
                        sql += " and dongia <=" + "100";
                    else
                        sql += " and dongia <=" + giaDen * 100;
                }
            }
             if (khuyenmai == "False")
                  sql += " and IDkhuyenmai = 2 ";
            if (khuyenmai == "True")
                sql += " and (IDkhuyenmai > 2 or IDkhuyenmai < 2)";

            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                int ketqua;
                ketqua = (int)dr[0];
                cnn.Close();
                return ketqua;
            }
            // dong ket noi
            cnn.Close();
            return 0;
        }

        
        public static bool kiemtraTonTai(int id)
        {
            bool result = false;
            SanPhamDto temp = LaySanPhamTheoID(id);
            if (temp != null)
                result = true;

            return result;
        }
        public static List<SanPhamDto> LayDanhSachSanPhamTheoNhaSanXuat(int idNhaSanXuat)
        {
            // tao ket noi CSDL 
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachSanPhamTheoNhaSanXuat");
            StoreUtilities.AddInputPara(cmd, "@idNhaSanXuat", SqlDbType.Int, idNhaSanXuat);
            SqlDataReader dr = cmd.ExecuteReader();

            List<SanPhamDto> danhsach = new List<SanPhamDto>();
            SanPhamDto sanpham = null;
            while (dr.Read())
            {
                sanpham = new SanPhamDto();
                sanpham.ID = (int)dr["ID"];
                sanpham.Hinh = (string)dr["Hinhanh"];
                sanpham.IDLoai = (int)dr["Maloai"];
                sanpham.IDNhaSanXuat = (int)dr["MaNSX"];
                sanpham.NgayNhap = (DateTime)dr["NgayNhap"];
                sanpham.SoLuongCon = (int)dr["SoLuongCon"];
                sanpham.SoLuongDaBan = (int)dr["SoLuongDaBan"];
                sanpham.TenSanPham = (string)dr["TenSanPham"];
                sanpham.IDGiamGia = (int)dr["IDGiamGia"];
                sanpham.IDKhuyenMai = (int)dr["IDKhuyenMai"];
                sanpham.DonGia = (int)dr["DonGia"];
                sanpham.DiemBinhChon = (int)dr["DiemBinhChon"];


                danhsach.Add(sanpham);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }
        //<NEW CODE>
        public static bool CapNhatSoLuongTon_DaBan(int id, int SoLuong)
        {
            SanPhamDto sanpham = SanPhamDao.LaySanPhamTheoID(id);
            sanpham.SoLuongCon = sanpham.SoLuongCon - SoLuong;
            sanpham.SoLuongDaBan = sanpham.SoLuongDaBan + SoLuong;
            return SanPhamDao.CapnhatSanPham(sanpham);
        }
        //</NEW CODE>
    }
}
